SherbyNet - /page/wikibook/entry.mu

<!DOCTYPE html>

<title>Select (SQL)</title>

</head>

Select (SQL)

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .ambox{border:1px solid #a2a9b1;border-left:10px solid #36c;background-color:#fbfbfb;box-sizing:border-box}.mw-parser-output .ambox+link+.ambox,.mw-parser-output .ambox+link+style+.ambox,.mw-parser-output .ambox+link+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+style+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+link+.ambox{margin-top:-1px}html body.mediawiki .mw-parser-output .ambox.mbox-small-left{margin:4px 1em 4px 0;overflow:hidden;width:238px;border-collapse:collapse;font-size:88%;line-height:1.25em}.mw-parser-output .ambox-speedy{border-left:10px solid #b32424;background-color:#fee7e6}.mw-parser-output .ambox-delete{border-left:10px solid #b32424}.mw-parser-output .ambox-content{border-left:10px solid #f28500}.mw-parser-output .ambox-style{border-left:10px solid #fc3}.mw-parser-output .ambox-move{border-left:10px solid #9932cc}.mw-parser-output .ambox-protection{border-left:10px solid #a2a9b1}.mw-parser-output .ambox .mbox-text{border:none;padding:0.25em 0.5em;width:100%}.mw-parser-output .ambox .mbox-image{border:none;padding:2px 0 2px 0.5em;text-align:center}.mw-parser-output .ambox .mbox-imageright{border:none;padding:2px 0.5em 2px 0;text-align:center}.mw-parser-output .ambox .mbox-empty-cell{border:none;padding:0;width:1px}.mw-parser-output .ambox .mbox-image-div{width:52px}@media(min-width:720px){.mw-parser-output .ambox{margin:0 10%}}@media print{body.ns-0 .mw-parser-output .ambox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style>

The <a href="SQL" title="SQL">SQL</a> SELECT statement returns a <a href="Result_set" title="Result set">result set</a> of rows, from one or more <a href="Table_(database)" title="Table (database)">tables</a>.<a href="#cite_note-1">[1]</a><a href="#cite_note-2">[2]</a>

A SELECT statement retrieves zero or more rows from one or more <a href="Database_Tables" class="mw-redirect" title="Database Tables">database tables</a> or database <a href="View_(database)" class="mw-redirect" title="View (database)">views</a>. In most applications, <code>SELECT</code> is the most commonly used <a href="Data_manipulation_language" title="Data manipulation language">data manipulation language</a> (DML) command. As SQL is a <a href="Declarative_programming" title="Declarative programming">declarative programming</a> language, <code>SELECT</code> queries specify a result set, but do not specify how to calculate it. The database translates the query into a "<a href="Query_plan" title="Query plan">query plan</a>" which may vary between executions, database versions and database software. This functionality is called the "<a href="Query_optimizer" class="mw-redirect" title="Query optimizer">query optimizer</a>" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.

The SELECT statement has many optional clauses:

<ul><li><code>SELECT</code> list is the list of <a href="Column_(database)" title="Column (database)">columns</a> or SQL expressions to be returned by the query. This is approximately the <a href="Relational_algebra" title="Relational algebra">relational algebra</a> <a href="Projection_(relational_algebra)" title="Projection (relational algebra)">projection</a> operation.</li>

<li><code><a href="Alias_(SQL)" title="Alias (SQL)">AS</a></code> optionally provides an alias for each column or expression in the <code>SELECT</code> list. This is the relational algebra <a href="Rename_(relational_algebra)" title="Rename (relational algebra)">rename</a> operation.</li>

<li><code><a href="From_(SQL)" title="From (SQL)">FROM</a></code> specifies from which table to get the data.<a href="#cite_note-3">[3]</a></li>

<li><code>WHERE</code> specifies which rows to retrieve. This is approximately the relational algebra <a href="Selection_(relational_algebra)" title="Selection (relational algebra)">selection</a> operation.</li>

<li><code><a href="Group_by_(SQL)" title="Group by (SQL)">GROUP BY</a></code> groups rows sharing a property so that an <a href="Aggregate_function" title="Aggregate function">aggregate function</a> can be applied to each group.</li>

<li><code><a href="Having_(SQL)" title="Having (SQL)">HAVING</a></code> selects among the groups defined by the GROUP BY clause.</li>

<li><code><a href="Order_by_(SQL)" class="mw-redirect" title="Order by (SQL)">ORDER BY</a></code> specifies how to order the returned rows.</li></ul>

<div class="mw-heading mw-heading2"><h2 id="Overview">Overview</h2></div>

<code>SELECT</code> is the most common operation in SQL, called "the query". <code>SELECT</code> retrieves data from one or more <a href="Table_(database)" title="Table (database)">tables</a>, or expressions. Standard <code>SELECT</code> statements have no persistent effects on the database. Some non-standard implementations of <code>SELECT</code> can have persistent effects, such as the <code>SELECT INTO</code> syntax provided in some databases.<a href="#cite_note-ms-sql-select-into-4">[4]</a>

Queries allow the user to describe desired data, leaving the <a href="Database_management_system" class="mw-redirect" title="Database management system">database management system (DBMS)</a> to carry out <a href="Query_plan" title="Query plan">planning</a>, <a href="Query_optimizer" class="mw-redirect" title="Query optimizer">optimizing</a>, and performing the physical operations necessary to produce that result as it chooses.

A query includes a list of columns to include in the final result, normally immediately following the <code>SELECT</code> keyword. An asterisk ("<code>*</code>") can be used to specify that the query should return all columns of all the queried tables. <code>SELECT</code> is the most complex statement in SQL, with optional keywords and clauses that include:

<ul><li>The <code><a href="From_(SQL)" title="From (SQL)">FROM</a></code> clause, which indicates the tables to retrieve data from. The <code>FROM</code> clause can include optional <code><a href="Join_(SQL)" title="Join (SQL)">JOIN</a></code> subclauses to specify the rules for joining tables.</li>

<li>The <code>WHERE</code> clause includes a comparison predicate, which restricts the rows returned by the query. The <code>WHERE</code> clause eliminates all rows from the result set where the comparison predicate does not evaluate to True.</li>

<li>The <code>GROUP BY</code> clause projects rows having common values into a smaller set of rows. <code>GROUP BY</code> is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The <code>WHERE</code> clause is applied before the <code>GROUP BY</code> clause.</li>

<li>The <code><a href="Having_(SQL)" title="Having (SQL)">HAVING</a></code> clause includes a predicate used to filter rows resulting from the <code>GROUP BY</code> clause. Because it acts on the results of the <code>GROUP BY</code> clause, aggregation functions can be used in the <code>HAVING</code> clause predicate.</li>

<li>The <code><a href="Order_by_(SQL)" class="mw-redirect" title="Order by (SQL)">ORDER BY</a></code> clause identifies which columns to use to sort the resulting data, and in which direction to sort them (ascending or descending). Without an <code>ORDER BY</code> clause, the order of rows returned by an SQL query is undefined.</li>

<li>The <code>DISTINCT</code> keyword<a href="#cite_note-5">[5]</a> eliminates duplicate data.<a href="#cite_note-6">[6]</a></li></ul>

The following example of a <code>SELECT</code> query returns a list of expensive books. The query retrieves all rows from the Book table in which the price column contains a value greater than 100.00. The result is sorted in ascending order by title. The asterisk (*) in the select list indicates that all columns of the Book table should be included in the result set.

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT *

FROM Book

WHERE price > 100.00

ORDER BY title;

</pre></div>

The example below demonstrates a query of multiple tables, grouping, and aggregation, by returning a list of books and the number of authors associated with each book.

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre> SELECT Book.title AS Title,

count(*) AS Authors

FROM Book

JOIN Book_author

ON Book.isbn = Book_author.isbn

GROUP BY Book.title;

</pre></div>

Example output might resemble the following:

<pre>Title Authors

---------------------- -------

SQL Examples and Guide 4

The Joy of SQL 1

An Introduction to SQL 2

Pitfalls of SQL 1

</pre>

Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Book table, one could re-write the query above in the following form:

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT title,

count(*) AS Authors

FROM Book

NATURAL JOIN Book_author

GROUP BY title;

</pre></div>

However, many vendors either do not support this approach, or require certain column-naming conventions for natural joins to work effectively.

SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example, which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT isbn,

title,

price,

price * 0.06 AS sales_tax

FROM Book

WHERE price > 100.00

ORDER BY title;

</pre></div>

<div class="mw-heading mw-heading3"><h3 id="Subqueries">Subqueries</h3></div>

Queries can be nested so that the results of one query can be used in another query via a <a href="Relational_operator" title="Relational operator">relational operator</a> or aggregation function. A nested query is also known as a subquery. While joins and other table operations provide computationally superior (i.e. faster) alternatives in many cases (all depending on implementation), the use of subqueries introduces a hierarchy in execution that can be useful or necessary. In the following example, the aggregation function <code>AVG</code> receives as input the result of a subquery:

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT isbn,

title,

price

FROM Book

WHERE price < (SELECT AVG(price) FROM Book)

ORDER BY title;

</pre></div>

A subquery can use values from the outer query, in which case it is known as a <a href="Correlated_subquery" title="Correlated subquery">correlated subquery</a>.

Since 1999 the SQL standard allows WITH clauses, i.e. named subqueries often called <a href="Common_table_expression" class="mw-redirect" title="Common table expression">common table expressions</a> (named and designed after the IBM DB2 version 2 implementation; Oracle calls these <a href="Subquery_factoring" class="mw-redirect" title="Subquery factoring">subquery factoring</a>). CTEs can also be <a href="Recursive" class="mw-redirect" title="Recursive">recursive</a> by referring to themselves; <a href="Hierarchical_and_recursive_queries_in_SQL" title="Hierarchical and recursive queries in SQL">the resulting mechanism</a> allows tree or graph traversals (when represented as relations), and more generally <a href="Fixpoint" class="mw-redirect" title="Fixpoint">fixpoint</a> computations.

<div class="mw-heading mw-heading3"><h3 id="Derived_table">Derived table</h3></div>

A derived table is a subquery in a FROM clause. Essentially, the derived table is a subquery that can be selected from or joined to. Derived table functionality allows the user to reference the subquery as a table. The derived table also is referred to as an inline view or a select in from list.

In the following example, the SQL statement involves a join from the initial Books table to the derived table "Sales". This derived table captures associated book sales information using the ISBN to join to the Books table. As a result, the derived table provides the result set with additional columns (the number of items sold and the company that sold the books):

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm

FROM Book b

JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN

FROM Book_Sales

GROUP BY Company_Nm, ISBN) sales

ON sales.isbn = b.isbn

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="Examples">Examples</h2></div>

<th>Table "T"

</th>

<th>Query

</th>

<th>Result

</th></tr>

<tr>

<th>C2

</th></tr>

<tr>

<td>a

</td></tr>

<tr>

<td>b

</td></tr></tbody></table>

</td>

<td><code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT * FROM T;</code>

</td>

<th>C2

</th></tr>

<tr>

<td>a

</td></tr>

<tr>

<td>b

</td></tr></tbody></table>

</td></tr>

<tr>

<th>C2

</th></tr>

<tr>

<td>a

</td></tr>

<tr>

<td>b

</td></tr></tbody></table>

</td>

<td><code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT C1 FROM T;</code>

</td>

<th>C1

</th></tr>

<tr>

<td>1

</td></tr>

<tr>

<td>2

</td></tr></tbody></table>

</td></tr>

<tr>

<th>C2

</th></tr>

<tr>

<td>a

</td></tr>

<tr>

<td>b

</td></tr></tbody></table>

</td>

<td><code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT * FROM T WHERE C1 = 1;</code>

</td>

<th>C2

</th></tr>

<tr>

<td>a

</td></tr></tbody></table>

</td></tr>

<tr>

<th>C2

</th></tr>

<tr>

<td>a

</td></tr>

<tr>

<td>b

</td></tr></tbody></table>

</td>

<td><code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT * FROM T ORDER BY C1 DESC;</code>

</td>

<th>C2

</th></tr>

<tr>

<td>b

</td></tr>

<tr>

<td>a

</td></tr></tbody></table>

</td></tr>

<tr>

<td align="center">does not exist

</td>

<td><code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT 1+1, 3*2;</code>

</td>

<th>`3*2`

</th></tr>

<tr>

<td>6

</td></tr></tbody></table>

Given a table T, the query <code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT * FROM T</code> will result in all the elements of all the rows of the table being shown.

With the same table, the query <code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT C1 FROM T</code> will result in the elements from the column C1 of all the rows of the table being shown. This is similar to a <a href="Projection_(relational_algebra)" title="Projection (relational algebra)">projection</a> in <a href="Relational_algebra" title="Relational algebra">relational algebra</a>, except that in the general case, the result may contain duplicate rows. This is also known as a Vertical Partition in some database terms, restricting query output to view only specified fields or columns.

With the same table, the query <code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT * FROM T WHERE C1 = 1</code> will result in all the elements of all the rows where the value of column C1 is '1' being shown – in <a href="Relational_algebra" title="Relational algebra">relational algebra</a> terms, a <a href="Selection_(relational_algebra)" title="Selection (relational algebra)">selection</a> will be performed, because of the WHERE clause. This is also known as a Horizontal Partition, restricting rows output by a query according to specified conditions.

With more than one table, the result set will be every combination of rows. So if two tables are T1 and T2, <code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr">SELECT * FROM T1, T2</code> will result in every combination of T1 rows with every T2 rows. E.g., if T1 has 3 rows and T2 has 5 rows, then 15 rows will result.

Although not in standard, most DBMS allows using a select clause without a table by pretending that an imaginary table with one row is used. This is mainly used to perform calculations where a table is not needed.

The SELECT clause specifies a list of properties (columns) by name, or the wildcard character (“*”) to mean “all properties”.

<div class="mw-heading mw-heading2"><h2 id="Limiting_result_rows">Limiting result rows</h2></div>

Often it is convenient to indicate a maximum number of rows that are returned. This can be used for testing or to prevent consuming excessive resources if the query returns more information than expected. The approach to do this often varies per vendor.

In <a href="International_Organization_for_Standardization" title="International Organization for Standardization">ISO</a> <a href="SQL%3A2003" title="SQL:2003">SQL:2003</a>, result sets may be limited by using

<ul><li><a href="Cursor_(databases)" title="Cursor (databases)">cursors</a>, or</li>

<li>by adding a <a href="SQL_window_function" class="mw-redirect" title="SQL window function">SQL window function</a> to the SELECT-statement</li></ul>

ISO <a href="SQL%3A2008" title="SQL:2008">SQL:2008</a> introduced the <code>FETCH FIRST</code> clause.

According to PostgreSQL v.9 documentation, an SQL window function "performs a calculation across a set of table rows that are somehow related to the current row", in a way similar to aggregate functions.<a href="#cite_note-7">[7]</a>

The name recalls signal processing <a href="Window_function" title="Window function">window functions</a>. A window function call always contains an OVER clause.

<div class="mw-heading mw-heading3"><h3 id="ROW_NUMBER()_window_function">ROW_NUMBER() window function</h3></div>

<code>ROW_NUMBER() OVER</code> may be used for a simple table on the returned rows, e.g. to return no more than ten rows:

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM

( SELECT

ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,

columns

FROM tablename

) AS foo

WHERE row_number <= 10

</pre></div>

ROW_NUMBER can be <a href="Nondeterministic_algorithm" title="Nondeterministic algorithm">non-deterministic</a>: if sort_key is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where sort_key is the same. When sort_key is unique, each row will always get a unique row number.

<div class="mw-heading mw-heading3"><h3 id="RANK()_window_function">RANK() window function</h3></div>

The <code>RANK() OVER</code> window function acts like ROW_NUMBER, but may return more or less than n rows in case of tie conditions, e.g. to return the top-10 youngest persons:

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM (

SELECT

RANK() OVER (ORDER BY age ASC) AS ranking,

person_id,

person_name,

age

FROM person

) AS foo

WHERE ranking <= 10

</pre></div>

The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows.

<div class="mw-heading mw-heading3"><h3 id="FETCH_FIRST_clause">FETCH FIRST clause</h3></div>

Since ISO <a href="SQL%3A2008" title="SQL:2008">SQL:2008</a> results limits can be specified as in the following example using the <code>FETCH FIRST</code> clause.

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM T

FETCH FIRST 10 ROWS ONLY

</pre></div>

This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c and <a href="Mimer_SQL" title="Mimer SQL">Mimer SQL</a>.

Microsoft SQL Server 2008 and higher <a rel="nofollow" class="external text" href="https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned">supports <code>FETCH FIRST</code></a>, but it is considered part of the <code>ORDER BY</code> clause. The <code>ORDER BY</code>, <code>OFFSET</code>, and <code>FETCH FIRST</code> clauses are all required for this usage.

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SELECT * FROM T

ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

</pre></div>

<div class="mw-heading mw-heading3"><h3 id="Non-standard_syntax">Non-standard syntax</h3></div>

Some DBMSs offer non-standard syntax either instead of or in addition to SQL standard syntax. Below, variants of the simple limit query for different DBMSes are listed:

<td><div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SET ROWCOUNT 10

SELECT * FROM T

</pre></div>

</td>

<td><a href="Microsoft_SQL_Server" title="Microsoft SQL Server">MS SQL Server</a> (This also works on Microsoft SQL Server 6.5 while the Select top 10 * from T does not)

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-postgres mw-content-ltr" dir="ltr"><pre>SELECT * FROM T

LIMIT 10 OFFSET 20

</pre></div>

</td>

<td><a href="Netezza" title="Netezza">Netezza</a>, <a href="MySQL" title="MySQL">MySQL</a>, <a href="MariaDB" title="MariaDB">MariaDB</a> (also supports the standard version, since version 10.6), <a href="SQL_Anywhere" title="SQL Anywhere">SAP SQL Anywhere</a>, <a href="PostgreSQL" title="PostgreSQL">PostgreSQL</a> (also supports the standard, since version 8.4), <a href="SQLite" title="SQLite">SQLite</a>, <a href="HSQLDB" title="HSQLDB">HSQLDB</a>, <a href="H2_(DBMS)" class="mw-redirect" title="H2 (DBMS)">H2</a>, <a href="Vertica" title="Vertica">Vertica</a>, <a href="Polyhedra_DBMS" class="mw-redirect" title="Polyhedra DBMS">Polyhedra</a>, <a href="Couchbase_Server" title="Couchbase Server">Couchbase Server</a>, <a href="Snowflake_Computing" class="mw-redirect" title="Snowflake Computing">Snowflake Computing</a>, <a href="Virtuoso_Universal_Server" title="Virtuoso Universal Server">OpenLink Virtuoso</a>

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * from T

WHERE ROWNUM <= 10

</pre></div>

</td>

<td><a href="Oracle_database" class="mw-redirect" title="Oracle database">Oracle</a>

</td></tr>

<tr>

<td><code>SELECT FIRST 10 * from T </code>

</td>

<td><a href="Ingres_(database)" title="Ingres (database)">Ingres</a>

</td></tr>

<tr>

<td><code>SELECT FIRST 10 * FROM T order by a </code>

</td>

<td><a href="IBM_Informix" class="mw-redirect" title="IBM Informix">Informix</a>

</td></tr>

<tr>

<td><code>SELECT SKIP 20 FIRST 10 * FROM T order by c, d </code>

</td>

<td><a href="IBM_Informix" class="mw-redirect" title="IBM Informix">Informix</a> (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)

</td></tr>

<tr>

<td><code>SELECT TOP 10 * FROM T</code>

</td>

<td><a href="Microsoft_SQL_Server" title="Microsoft SQL Server">MS SQL Server</a>, <a href="Adaptive_Server_Enterprise" title="Adaptive Server Enterprise">SAP ASE</a>, <a href="Microsoft_Access" title="Microsoft Access">MS Access</a>, <a href="SAP_IQ" title="SAP IQ">SAP IQ</a>, <a href="Teradata" title="Teradata">Teradata</a>

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM T

SAMPLE 10

</pre></div>

</td>

<td><a href="Teradata" title="Teradata">Teradata</a>

</td></tr>

<tr>

<td><code>SELECT TOP 20, 10 * FROM T</code>

</td>

<td><a href="Virtuoso_Universal_Server" title="Virtuoso Universal Server">OpenLink Virtuoso</a> (skips 20, delivers next 10)<a href="#cite_note-docs_9.19-8">[8]</a>

</td></tr>

<tr>

<td><code>SELECT TOP 10 START AT 20 * FROM T</code>

</td>

<td><a href="SQL_Anywhere" title="SQL Anywhere">SAP SQL Anywhere</a> (also supports the standard, since version 9.0.1)

</td></tr>

<tr>

<td><code>SELECT FIRST 10 SKIP 20 * FROM T</code>

</td>

<td><a href="Firebird_(database_server)" title="Firebird (database server)">Firebird</a>

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM T

ROWS 20 TO 30

</pre></div>

</td>

<td><a href="Firebird_(database_server)" title="Firebird (database server)">Firebird</a> (since version 2.1)

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM T

WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY

</pre></div>

</td>

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM T

WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY

</pre></div>

</td>

<td><a href="IBM_Db2" title="IBM Db2">IBM Db2</a> (new rows are filtered after comparing with key column of table T)

</td></tr></tbody></table>

<div class="mw-heading mw-heading3"><h3 id="Rows_Pagination">Rows Pagination</h3></div>

Rows Pagination<a href="#cite_note-9">[9]</a> is an approach used to limit and display only a part of the total data of a query in the database. Instead of showing hundreds or thousands of rows at the same time, the server is requested only one page (a limited set of rows, per example only 10 rows), and the user starts navigating by requesting the next page, and then the next one, and so on. It is very useful, specially in web systems, where there is no dedicated connection between the client and the server, so the client does not have to wait to read and display all the rows of the server.

<div class="mw-heading mw-heading4"><h4 id="Data_in_Pagination_approach">Data in Pagination approach</h4></div>

<ul><li><code>{rows}</code> = Number of rows in a page</li>

<li><code>{page_number}</code> = Number of the current page</li>

<li><code>{begin_base_0}</code> = Number of the row - 1 where the page starts = (page_number-1) * rows</li></ul>

<div class="mw-heading mw-heading4"><h4 id="Simplest_method_(but_very_inefficient)">Simplest method (but very inefficient)</h4></div>

<ol><li>Select all rows from the database</li>

<li>Read all rows but send to display only when the row_number of the rows read is between <code>{begin_base_0 + 1}</code> and <code>{begin_base_0 + rows}</code></li></ol>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>Select *

from {table}

order by {unique_key}

</pre></div>

<div class="mw-heading mw-heading4"><h4 id="Other_simple_method_(a_little_more_efficient_than_read_all_rows)">Other simple method (a little more efficient than read all rows)</h4></div>

<ol><li>Select all the rows from the beginning of the table to the last row to display (<code>{begin_base_0 + rows}</code>)</li>

<li>Read the <code>{begin_base_0 + rows}</code> rows but send to display only when the row_number of the rows read is greater than <code>{begin_base_0}</code></li></ol>

<th>SQL

</th>

<th>Dialect

</th></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select *

from {table}

order by {unique_key}

FETCH FIRST {begin_base_0 + rows} ROWS ONLY

</pre></div>

</td>

<td>SQL ANSI 2008 PostgreSQL SQL Server 2012 Derby Oracle 12c DB2 12 Mimer SQL

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre>Select *

from {table}

order by {unique_key}

LIMIT {begin_base_0 + rows}

</pre></div>

</td>

<td>MySQL SQLite

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>Select TOP {begin_base_0 + rows} *

from {table}

order by {unique_key}

</pre></div>

</td>

<td>SQL Server 2005

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre>Select *

from {table}

order by {unique_key}

ROWS LIMIT {begin_base_0 + rows}

</pre></div>

</td>

<td>Sybase, ASE 16 SP2

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SET ROWCOUNT {begin_base_0 + rows}

Select *

from {table}

order by {unique_key}

SET ROWCOUNT 0

</pre></div>

</td>

<td>Sybase, SQL Server 2000

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>Select *

FROM (

SELECT *

FROM {table}

ORDER BY {unique_key}

) a

where rownum <= {begin_base_0 + rows}

</pre></div>

</td>

<td>Oracle 11

</td></tr></tbody></table>

<div class="mw-heading mw-heading4"><h4 id="Method_with_positioning">Method with positioning</h4></div>

<ol><li>Select only <code>{rows}</code> rows starting from the next row to display (<code>{begin_base_0 + 1}</code>)</li>

<li>Read and send to display all the rows read from the database</li></ol>

<th>SQL

</th>

<th>Dialect

</th></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-postgres mw-content-ltr" dir="ltr"><pre>Select *

from {table}

order by {unique_key}

OFFSET {begin_base_0} ROWS

FETCH NEXT {rows} ROWS ONLY

</pre></div>

</td>

<td>SQL ANSI 2008 PostgreSQL SQL Server 2012 Derby Oracle 12c DB2 12 Mimer SQL

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-postgres mw-content-ltr" dir="ltr"><pre>Select *

from {table}

order by {unique_key}

LIMIT {rows} OFFSET {begin_base_0}

</pre></div>

</td>

<td>MySQL MariaDB PostgreSQL SQLite

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre>Select *

from {table}

order by {unique_key}

LIMIT {begin_base_0}, {rows}

</pre></div>

</td>

<td>MySQL MariaDB SQLite

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre>Select *

from {table}

order by {unique_key}

ROWS LIMIT {rows} OFFSET {begin_base_0}

</pre></div>

</td>

<td>Sybase, ASE 16 SP2

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>Select TOP {begin_base_0 + rows}

*, _offset=identity(10)

into #temp

from {table}

ORDER BY {unique_key}

select * from #temp where _offset > {begin_base_0}

DROP TABLE #temp

</pre></div>

</td>

<td>Sybase 12.5.3:

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SET ROWCOUNT {begin_base_0 + rows}

select *, _offset=identity(10)

into #temp

from {table}

ORDER BY {unique_key}

select * from #temp where _offset > {begin_base_0}

DROP TABLE #temp

SET ROWCOUNT 0

</pre></div>

</td>

<td>Sybase 12.5.2:

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>select TOP {rows} *

from (

select *, ROW_NUMBER() over (order by {unique_key}) as _offset

from {table}

) xx

where _offset > {begin_base_0}

</pre></div>

</td>

<td>SQL Server 2005

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SET ROWCOUNT {begin_base_0 + rows}

select *, _offset=identity(int,1,1)

into #temp

from {table}

ORDER BY {unique-key}

select * from #temp where _offset > {begin_base_0}

DROP TABLE #temp

SET ROWCOUNT 0

</pre></div>

</td>

<td>SQL Server 2000

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>SELECT * FROM (

SELECT rownum-1 as _offset, a.*

FROM(

SELECT *

FROM {table}

ORDER BY {unique_key}

) a

WHERE rownum <= {begin_base_0 + cant_regs}

)

WHERE _offset >= {begin_base_0}

</pre></div>

</td>

<td>Oracle 11

</td></tr></tbody></table>

<div class="mw-heading mw-heading4"><h4 id="Method_with_filter_(it_is_more_sophisticated_but_necessary_for_very_big_dataset)">Method with filter (it is more sophisticated but necessary for very big dataset)</h4></div>

<ol><li>Select only then <code>{rows}</code> rows with filter:

<ol><li>First Page: select only the first <code>{rows}</code> rows, depending on the type of database</li>

<li>Next Page: select only the first <code>{rows}</code> rows, depending on the type of database, where the <code>{unique_key}</code> is greater than <code>{last_val}</code> (the value of the <code>{unique_key}</code> of the last row in the current page)</li>

<li>Previous Page: sort the data in the reverse order, select only the first <code>{rows}</code> rows, where the <code>{unique_key}</code> is less than <code>{first_val}</code> (the value of the <code>{unique_key}</code> of the first row in the current page), and sort the result in the correct order</li></ol></li>

<li>Read and send to display all the rows read from the database</li></ol>

<th>First Page

</th>

<th>Next Page

</th>

<th>Previous Page

</th>

<th>Dialect

</th></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select *

from {table}

order by {unique_key}

FETCH FIRST {rows} ROWS ONLY

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select *

from {table}

where {unique_key} > {last_val}

order by {unique_key}

FETCH FIRST {rows} ROWS ONLY

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select *

from (

select *

from {table}

where {unique_key} < {first_val}

order by {unique_key} DESC

FETCH FIRST {rows} ROWS ONLY

) a

order by {unique_key}

</pre></div>

</td>

<td>SQL ANSI 2008 PostgreSQL SQL Server 2012 Derby Oracle 12c DB2 12 Mimer SQL

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre>select *

from {table}

order by {unique_key}

LIMIT {rows}

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre>select *

from {table}

where {unique_key} > {last_val}

order by {unique_key}

LIMIT {rows}

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre>select *

from (

select *

from {table}

where {unique_key} < {first_val}

order by {unique_key} DESC

LIMIT {rows}

) a

order by {unique_key}

</pre></div>

</td>

<td>MySQL SQLite

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>select TOP {rows} *

from {table}

order by {unique_key}

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>select TOP {rows} *

from {table}

where {unique_key} > {last_val}

order by {unique_key}

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>select *

from (

select TOP {rows} *

from {table}

where {unique_key} < {first_val}

order by {unique_key} DESC

) a

order by {unique_key}

</pre></div>

</td>

<td>SQL Server 2005

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SET ROWCOUNT {rows}

select *

from {table}

order by {unique_key}

SET ROWCOUNT 0

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SET ROWCOUNT {rows}

select *

from {table}

where {unique_key} > {last_val}

order by {unique_key}

SET ROWCOUNT 0

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>SET ROWCOUNT {rows}

select *

from (

select *

from {table}

where {unique_key} < {first_val}

order by {unique_key} DESC

) a

order by {unique_key}

SET ROWCOUNT 0

</pre></div>

</td>

<td>Sybase, SQL Server 2000

</td></tr>

<tr>

<td>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>select *

from (

select *

from {table}

order by {unique_key}

) a

where rownum <= {rows}

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>select *

from (

select *

from {table}

where {unique_key} > {last_val}

order by {unique_key}

) a

where rownum <= {rows}

</pre></div>

</td>

<td>

<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre>select *

from (

select *

from (

select *

from {table}

where {unique_key} < {first_val}

order by {unique_key} DESC

) a1

where rownum <= {rows}

) a2

order by {unique_key}

</pre></div>

</td>

<td>Oracle 11

</td></tr></tbody></table>

<div class="mw-heading mw-heading2"><h2 id="Hierarchical_query">Hierarchical query</h2></div>

Some databases provide <a href="Hierarchical_query" class="mw-redirect" title="Hierarchical query">specialised syntax</a> for <a href="Hierarchical_data" class="mw-redirect" title="Hierarchical data">hierarchical data</a>.

A window function in <a href="SQL%3A2003" title="SQL:2003">SQL:2003</a> is an <a href="Aggregate_function" title="Aggregate function">aggregate function</a> applied to a partition of the result set.

For example,

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre> sum(population) OVER( PARTITION BY city )

</pre></div>

calculates the sum of the populations of all rows having the same city value as the current row.

Partitions are specified using the OVER clause which modifies the aggregate. Syntax:

<div class="mw-highlight mw-highlight-lang-bnf mw-content-ltr" dir="ltr"><pre><OVER_CLAUSE> :: =

OVER ( [ PARTITION BY <expr>, ... ]

[ ORDER BY <expression> ] )

</pre></div>

The OVER clause can partition and order the result set. Ordering is used for order-relative functions such as row_number.

<div class="mw-heading mw-heading2"><h2 id="Query_evaluation_ANSI">Query evaluation ANSI</h2></div>

The processing of a SELECT statement according to ANSI SQL would be the following:<a href="#cite_note-10">[10]</a>

<div><ol><li><div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select g.*

from users u inner join groups g on g.Userid = u.Userid

where u.LastName = 'Smith'

and u.FirstName = 'John'

</pre></div></li><li>the FROM clause is evaluated, a cross join or Cartesian product is produced for the first two tables in the FROM clause resulting in a virtual table as Vtable1</li><li>the ON clause is evaluated for vtable1; only records which meet the join condition g.Userid = u.Userid are inserted into Vtable2</li><li>If an outer join is specified, records which were dropped from vTable2 are added into VTable 3, for instance if the above query were:

<div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select u.*

from users u left join groups g on g.Userid = u.Userid

where u.LastName = 'Smith'

and u.FirstName = 'John'

</pre></div>

all users who did not belong to any groups would be added back into Vtable3</li><li>the WHERE clause is evaluated, in this case only group information for user John Smith would be added to vTable4</li><li>the GROUP BY is evaluated; if the above query were:

<div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select g.GroupName, count(g.*) as NumberOfMembers

from users u inner join groups g on g.Userid = u.Userid

group by GroupName

</pre></div>

vTable5 would consist of members returned from vTable4 arranged by the grouping, in this case the GroupName</li><li>the HAVING clause is evaluated for groups for which the HAVING clause is true and inserted into vTable6. For example:

<div class="mw-highlight mw-highlight-lang-postgresql mw-content-ltr" dir="ltr"><pre>select g.GroupName, count(g.*) as NumberOfMembers

from users u inner join groups g on g.Userid = u.Userid

group by GroupName

having count(g.*) > 5

</pre></div></li><li>the SELECT list is evaluated and returned as Vtable 7</li><li>the DISTINCT clause is evaluated; duplicate rows are removed and returned as Vtable 8</li><li>the ORDER BY clause is evaluated, ordering the rows and returning VCursor9. This is a cursor and not a table because ANSI defines a cursor as an ordered set of rows (not relational).</li></ol></div>

<div class="mw-heading mw-heading2"><h2 id="Window_function_support_by_RDBMS_vendors">Window function support by RDBMS vendors</h2></div>

The implementation of window function features by vendors of relational databases and SQL engines differs wildly. Most databases support at least some flavour of window functions. However, when we take a closer look it becomes clear that most vendors only implement a subset of the standard. Let's take the powerful RANGE clause as an example. Only Oracle, DB2, Spark/Hive, and Google Big Query fully implement this feature. More recently, vendors have added new extensions to the standard, e.g. array aggregation functions. These are particularly useful in the context of running SQL against a distributed file system (Hadoop, Spark, Google BigQuery) where we have weaker data co-locality guarantees than on a distributed relational database (MPP). Rather than evenly distributing the data across all nodes, SQL engines running queries against a distributed filesystem can achieve data co-locality guarantees by nesting data and thus avoiding potentially expensive joins involving heavy shuffling across the network. User-defined aggregate functions that can be used in window functions are another extremely powerful feature.

<div class="mw-heading mw-heading2"><h2 id="Generating_data_in_T-SQL">Generating data in T-SQL</h2></div>

Method to generate data based on the union all

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>select 1 a, 1 b union all

select 1, 2 union all

select 1, 3 union all

select 2, 1 union all

select 5, 1

</pre></div>

SQL Server 2008 supports the "row constructor" feature, specified in the <a href="SQL%3A1999" title="SQL:1999">SQL:1999</a> standard

<div class="mw-highlight mw-highlight-lang-tsql mw-content-ltr" dir="ltr"><pre>select *

from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b)

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("./mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("./mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("./mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("./mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite id="CITEREFMicrosoft2023" class="citation web cs1">Microsoft (23 May 2023). <a rel="nofollow" class="external text" href="http://msdn2.microsoft.com/en-us/library/ms189499.aspx">"Transact-SQL Syntax Conventions"</a>.</cite>

</li>

<li id="cite_note-2"><a href="#cite_ref-2">^</a> <cite id="CITEREFMySQL" class="citation web cs1">MySQL. <a rel="nofollow" class="external text" href="http://dev.mysql.com/doc/refman/5.0/en/select.html">"SQL SELECT Syntax"</a>.</cite>

</li>

<li id="cite_note-3"><a href="#cite_ref-3">^</a> Omitting FROM clause is not standard, but allowed by most major DBMSes.

</li>

<li id="cite_note-ms-sql-select-into-4"><a href="#cite_ref-ms-sql-select-into_4-0">^</a> <cite class="citation book cs1">"Transact-SQL Reference". <a rel="nofollow" class="external text" href="http://msdn.microsoft.com/en-us/library/ms188029.aspx">SQL Server Language Reference</a>. SQL Server 2005 Books Online. Microsoft. 2007-09-15. Retrieved 2007-06-17.</cite>

</li>

<cite class="citation book cs1"><a rel="nofollow" class="external text" href="https://books.google.com/books?id=ESjMAAAAQBAJ">SAS 9.4 SQL Procedure User's Guide</a>. SAS Institute (published 2013). 10 July 2013. p. 248. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>9781612905686</bdi>. Retrieved 2015-10-21. <q>Although the UNIQUE argument is identical to DISTINCT, it is not an ANSI standard.</q></cite>

</li>

<cite id="CITEREFLeonLeon1999" class="citation book cs1"><a href="Alexis_Leon" title="Alexis Leon">Leon, Alexis</a>; Leon, Mathews (1999). "Eliminating duplicates - SELECT using DISTINCT". <a rel="nofollow" class="external text" href="https://books.google.com/books?id=dmiPz2MMpfwC">SQL: A Complete Reference</a>. New Delhi: Tata McGraw-Hill Education (published 2008). p. 143. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>9780074637081</bdi>. Retrieved 2015-10-21. <q>[...] the keyword DISTINCT [...] eliminates the duplicates from the result set.</q></cite>

</li>

<li id="cite_note-7"><a href="#cite_ref-7">^</a> <a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/9.1/static/tutorial-window.html">PostgreSQL 9.1.24 Documentation - Chapter 3. Advanced Features</a>

</li>

<li id="cite_note-docs_9.19-8"><a href="#cite_ref-docs_9.19_8-0">^</a> <cite id="CITEREFOpenLink_Software" class="citation web cs1">OpenLink Software. <a rel="nofollow" class="external text" href="http://docs.openlinksw.com/virtuoso/topselectoption/">"9.19.10. The TOP SELECT Option"</a>. docs.openlinksw.com. Retrieved 1 October 2019.</cite>

</li>

<li id="cite_note-9"><a href="#cite_ref-9">^</a> Ing. Óscar Bonilla, MBA

</li>

<li id="cite_note-10"><a href="#cite_ref-10">^</a> Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan, Lubor Kollar, and Dejan Sarka

</li>

</ol></div></div>

<div class="mw-heading mw-heading2"><h2 id="Sources">Sources</h2></div>

<ul><li>Horizontal & Vertical Partitioning, Microsoft SQL Server 2000 Books Online.</li></ul>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

<ul><li><a rel="nofollow" class="external text" href="http://wwwlgis.informatik.uni-kl.de/cms/fileadmin/courses/SS2008/NEDM/RDDM.Chapter.06.Windows_and_Query_Functions_in_SQL.pdf">Windowed Tables and Window function in SQL</a>, Stefan Deßloch</li>

<li><a rel="nofollow" class="external text" href="https://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10002.htm">Oracle SELECT syntax</a></li>

<li><a rel="nofollow" class="external text" href="https://www.firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb20x-dml-select-syntax">Firebird SELECT syntax</a></li>

<li><a rel="nofollow" class="external text" href="https://dev.mysql.com/doc/refman/8.0/en/select.html">MySQL SELECT syntax</a></li>

<li><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/sql-select.html">PostgreSQL SELECT syntax</a></li>

<li><a rel="nofollow" class="external text" href="https://www.sqlite.org/lang_select.html">SQLite SELECT syntax</a></li></ul>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hlist dl,.mw-parser-output .hlist ol,.mw-parser-output .hlist ul{margin:0;padding:0}.mw-parser-output .hlist dd,.mw-parser-output .hlist dt,.mw-parser-output .hlist li{margin:0;display:inline}.mw-parser-output .hlist.inline,.mw-parser-output .hlist.inline dl,.mw-parser-output .hlist.inline ol,.mw-parser-output .hlist.inline ul,.mw-parser-output .hlist dl dl,.mw-parser-output .hlist dl ol,.mw-parser-output .hlist dl ul,.mw-parser-output .hlist ol dl,.mw-parser-output .hlist ol ol,.mw-parser-output .hlist ol ul,.mw-parser-output .hlist ul dl,.mw-parser-output .hlist ul ol,.mw-parser-output .hlist ul ul{display:inline}.mw-parser-output .hlist .mw-empty-li{display:none}.mw-parser-output .hlist dt::after{content:": "}.mw-parser-output .hlist dd::after,.mw-parser-output .hlist li::after{content:" · ";font-weight:bold}.mw-parser-output .hlist dd:last-child::after,.mw-parser-output .hlist dt:last-child::after,.mw-parser-output .hlist li:last-child::after{content:none}.mw-parser-output .hlist dd dd:first-child::before,.mw-parser-output .hlist dd dt:first-child::before,.mw-parser-output .hlist dd li:first-child::before,.mw-parser-output .hlist dt dd:first-child::before,.mw-parser-output .hlist dt dt:first-child::before,.mw-parser-output .hlist dt li:first-child::before,.mw-parser-output .hlist li dd:first-child::before,.mw-parser-output .hlist li dt:first-child::before,.mw-parser-output .hlist li li:first-child::before{content:" (";font-weight:normal}.mw-parser-output .hlist dd dd:last-child::after,.mw-parser-output .hlist dd dt:last-child::after,.mw-parser-output .hlist dd li:last-child::after,.mw-parser-output .hlist dt dd:last-child::after,.mw-parser-output .hlist dt dt:last-child::after,.mw-parser-output .hlist dt li:last-child::after,.mw-parser-output .hlist li dd:last-child::after,.mw-parser-output .hlist li dt:last-child::after,.mw-parser-output .hlist li li:last-child::after{content:")";font-weight:normal}.mw-parser-output .hlist ol{counter-reset:listitem}.mw-parser-output .hlist ol>li{counter-increment:listitem}.mw-parser-output .hlist ol>li::before{content:" "counter(listitem)"\a0 "}.mw-parser-output .hlist dd ol>li:first-child::before,.mw-parser-output .hlist dt ol>li:first-child::before,.mw-parser-output .hlist li ol>li:first-child::before{content:" ("counter(listitem)"\a0 "}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1236075235">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbox{box-sizing:border-box;border:1px solid #a2a9b1;width:100%;clear:both;font-size:88%;text-align:center;padding:1px;margin:1em auto 0}.mw-parser-output .navbox .navbox{margin-top:0}.mw-parser-output .navbox+.navbox,.mw-parser-output .navbox+.navbox-styles+.navbox{margin-top:-1px}.mw-parser-output .navbox-inner,.mw-parser-output .navbox-subgroup{width:100%}.mw-parser-output .navbox-group,.mw-parser-output .navbox-title,.mw-parser-output .navbox-abovebelow{padding:0.25em 1em;line-height:1.5em;text-align:center}.mw-parser-output .navbox-group{white-space:nowrap;text-align:right}.mw-parser-output .navbox,.mw-parser-output .navbox-subgroup{background-color:#fdfdfd}.mw-parser-output .navbox-list{line-height:1.5em;border-color:#fdfdfd}.mw-parser-output .navbox-list-with-group{text-align:left;border-left-width:2px;border-left-style:solid}.mw-parser-output tr+tr>.navbox-abovebelow,.mw-parser-output tr+tr>.navbox-group,.mw-parser-output tr+tr>.navbox-image,.mw-parser-output tr+tr>.navbox-list{border-top:2px solid #fdfdfd}.mw-parser-output .navbox-title{background-color:#ccf}.mw-parser-output .navbox-abovebelow,.mw-parser-output .navbox-group,.mw-parser-output .navbox-subgroup .navbox-title{background-color:#ddf}.mw-parser-output .navbox-subgroup .navbox-group,.mw-parser-output .navbox-subgroup .navbox-abovebelow{background-color:#e6e6ff}.mw-parser-output .navbox-even{background-color:#f7f7f7}.mw-parser-output .navbox-odd{background-color:transparent}.mw-parser-output .navbox .hlist td dl,.mw-parser-output .navbox .hlist td ol,.mw-parser-output .navbox .hlist td ul,.mw-parser-output .navbox td.hlist dl,.mw-parser-output .navbox td.hlist ol,.mw-parser-output .navbox td.hlist ul{padding:0.125em 0}.mw-parser-output .navbox .navbar{display:block;font-size:100%}.mw-parser-output .navbox-title .navbar{float:left;text-align:left;margin-right:0.5em}body.skin--responsive .mw-parser-output .navbox-image img{max-width:none!important}@media print{body.ns-0 .mw-parser-output .navbox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r920966791">

/* start https://en.wikipedia.org/ */

.mw-parser-output span.smallcaps{font-variant:small-caps}.mw-parser-output span.smallcaps-smaller{font-size:85%}

/* end https://en.wikipedia.org/ */

</style></div><div role="navigation" class="navbox" aria-labelledby="SQL70" style="padding:3px"><table class="nowraplinks mw-collapsible autocollapse navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="col" class="navbox-title" colspan="2"><style data-mw-deduplicate="TemplateStyles:r1239400231">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbar{display:inline;font-size:88%;font-weight:normal}.mw-parser-output .navbar-collapse{float:left;text-align:left}.mw-parser-output .navbar-boxtext{word-spacing:0}.mw-parser-output .navbar ul{display:inline-block;white-space:nowrap;line-height:inherit}.mw-parser-output .navbar-brackets::before{margin-right:-0.125em;content:"[ "}.mw-parser-output .navbar-brackets::after{margin-left:-0.125em;content:" ]"}.mw-parser-output .navbar li{word-spacing:-0.125em}.mw-parser-output .navbar a>span,.mw-parser-output .navbar a>abbr{text-decoration:inherit}.mw-parser-output .navbar-mini abbr{font-variant:small-caps;border-bottom:none;text-decoration:none;cursor:inherit}.mw-parser-output .navbar-ct-full{font-size:114%;margin:0 7em}.mw-parser-output .navbar-ct-mini{font-size:114%;margin:0 4em}html.skin-theme-clientpref-night .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}@media(prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}}@media print{.mw-parser-output .navbar{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div id="SQL70" style="font-size:114%;margin:0 4em"><a href="SQL" title="SQL">SQL</a></div></th></tr><tr><th scope="row" class="navbox-group" style="width:1%">Versions</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="SEQUEL" class="mw-redirect" title="SEQUEL">SEQUEL</a></li>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%"><a href="SQL_reserved_words" class="mw-redirect" title="SQL reserved words">Keywords</a></th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<li><a href="Create_(SQL)" class="mw-redirect" title="Create (SQL)">Create</a></li>

<li><a href="Delete_(SQL)" title="Delete (SQL)">Delete</a></li>

<li><a href="Group_by_(SQL)" title="Group by (SQL)">Group by</a></li>

<li><a href="Having_(SQL)" title="Having (SQL)">Having</a></li>

<li><a href="Insert_(SQL)" title="Insert (SQL)">Insert</a></li>

<li><a href="Merge_(SQL)" title="Merge (SQL)">Merge</a></li>

<li><a href="Order_by" title="Order by">Order by</a></li>

<li><a href="Prepare_(SQL)" class="mw-redirect" title="Prepare (SQL)">Prepare</a></li>

<li><a href="Truncate_(SQL)" title="Truncate (SQL)">Truncate</a></li>

<li><a href="Set_operations_(SQL)" title="Set operations (SQL)">Union</a></li>

<li><a href="Update_(SQL)" title="Update (SQL)">Update</a></li>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Related</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="Edgar_F._Codd" title="Edgar F. Codd">Edgar Codd</a></li>

<li><a href="Relational_database" title="Relational database">Relational database</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">ISO/IEC SQL parts</th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li>Framework</li>

<li>Foundation</li>

<li><a href="SQL/CLI" class="mw-redirect" title="SQL/CLI">Call-Level Interface</a></li>

<li><a href="SQL/PSM" title="SQL/PSM">Persistent Stored Modules</a></li>

<li><a href="SQL/MED" title="SQL/MED">Management of External Data</a></li>

<li><a href="SQL/OLB" title="SQL/OLB">Object Language Bindings</a></li>

<li><a href="SQL/Schemata" title="SQL/Schemata">Information and Definition Schemas</a></li>

<li><a href="SQL/JRT" title="SQL/JRT">SQL Routines and Types for the Java Programming Language</a></li>

<li><a href="SQL/XML" title="SQL/XML">XML-Related Specifications</a></li></ul>

</div></td></tr></tbody></table></div></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-01-25" href="https://en.wikipedia.org/wiki/?title=Select_(SQL)&oldid=1271809630">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>